﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class aulaEfetivar : System.Web.UI.Page
    {
        public SqlConnection conexao = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
        public SqlCommand cmdPrincipal;
        public SqlDataReader drPrincipal;
        public string sql;

        public SqlConnection conexaoGrupo = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
        public SqlCommand cmdPrincipalGrupo;
        public SqlDataReader drPrincipalGrupo;
        public string sqlGrupo;

        public string efetiva;
        public string observacao;

        public SqlConnection conexaoUpdate = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
        public SqlCommand cmdUpdate;
        public string update;

        public validarValores vV = new validarValores();

        int contador;
        int resto;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                // Inserindo a data atual do computador
                txtData.Text = DateTime.Now.ToString("dd/MM/yyyy");

                

            }
        }

        void aula()
        {
            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();
            try
            {
                conexao.Open();
                conexaoGrupo.Open();


                sql = "SELECT codigoAula, codigoProfessor, (select nomeProfessor from professor where aula.codigoProfessor = professor.codigoProfessor) as nomeProfessor, \n";
                sql += "codigoAluno, (select nomeAluno from aluno where aula.codigoAluno = aluno.codigoAluno) as nomeAluno, codigoGrupo, dataAula, horaInicialAula, \n";
                sql += "horaFinalAula, codigoDisciplina, descricaoAssuntoAula, observacoesAula,  \n";
                sql += "aulaRealizada, valorHora, valorTotalSemDesconto, valorTotalComDesconto, codigoAnoLetivo, falta, meiaHora \n";
                sql += "FROM aula WHERE (dataAula = '" + txtData.Text + "')order by CONVERT(INT, SUBSTRING(horaInicialAula,1 , 2) + SUBSTRING(horaInicialAula,4 , 2))";


                
                contador = 0;

                string tabela = "<table>";
                string conteudo = "";
                string horaIniciaAula = "";
                string horaFinalAula = "";
                string horaIniciaAulaAnterior = "";
                string horaFinalAulaAnterior = "";
                cmdPrincipal = new SqlCommand(sql, conexao);
                drPrincipal = cmdPrincipal.ExecuteReader();


                if (drPrincipal.HasRows)
                {
                    while (drPrincipal.Read()) // lendo dados do select dentro do loop
                    {
                        horaIniciaAula = drPrincipal["horaInicialAula"].ToString();
                        horaFinalAula = drPrincipal["horaFinalAula"].ToString();


                        if (contador == 0)
                        {
                            tabela += "<tr>";
                        }


                        if (contador == 0)
                        {
                            conteudo += "<td style=\"background: #dae2f0;\">";
                        }
                        if (contador == 1)
                        {
                            conteudo += "<td style=\"background: #eecece;\">";
                        }
                        if (contador == 2)
                        {
                            conteudo += "<td style=\"background: #f3e4a7;\">";
                        }
                        
                        conteudo += "<div class=\"divisao\">";

                        if (horaFinalAula != horaFinalAulaAnterior)
                        {
                        conteudo += "<h3 class=\"horarios\">" + horaIniciaAula + " às " + horaFinalAula + "</h3>\n";
                            
                        }
                        
                        conteudo += "<table width=\"100%\" border=\"1\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#000000\">\n";
                        conteudo += "<tbody><tr align=\"center\" valign=\"middle\" class=\"th6\">\n";
                        conteudo += "<th style=\"background-color:#551A8B\">Professor (a)</th>\n";
                        conteudo += "<th style=\"background-color:#551A8B\">Aluno (a) / Grupo</th>\n";
                        conteudo += "<th style=\"background-color:#551A8B\" >Efetiva</th>\n";
                        conteudo += "<th style=\"background-color:#551A8B\" >Obs</th>\n";
                        conteudo += "</tr>\n";
                        conteudo += "<tr align=\"center\" valign=\"middle\" class=\"linha06\">\n";
                        conteudo += "<td style=\"width:400px;\">" + drPrincipal["nomeProfessor"].ToString() + "</td>\n";

                        // Selecionando os alunos do grupo
                        if (drPrincipal["nomeAluno"].ToString() == "")
                        {
                            sqlGrupo = "SELECT codigoAlunoGrupo, codigoGrupo, codigoAluno, ";
                            sqlGrupo += "(SELECT nomeAluno FROM aluno WHERE (alunoGrupo.codigoAluno = aluno.codigoAluno)) AS nomeAluno ";
                            sqlGrupo += "FROM alunoGrupo where codigoGrupo = " + drPrincipal["codigoGrupo"].ToString();

                            cmdPrincipalGrupo = new SqlCommand(sqlGrupo, conexaoGrupo);
                            drPrincipalGrupo = cmdPrincipalGrupo.ExecuteReader();

                            conteudo += "<td style=\"width:400px;\">";

                            if (drPrincipalGrupo.HasRows)
                            {
                                while (drPrincipalGrupo.Read())
                                {
                                    conteudo += drPrincipalGrupo["nomeAluno"].ToString() + "&nbsp;&nbsp; ";
                                    conteudo += listar(drPrincipalGrupo["codigoAluno"].ToString(), drPrincipal["codigoAula"].ToString());
                                }
                            }

                            drPrincipalGrupo.Close();

                            //AQUI VAMOS VERIFICAR SE O ALUNO ESTA INADIMPLENTE


                            conteudo += "</td>\n";
                        }
                        else
                        {
                            //AQUI VAMOS VERIFICAR SE O ALUNO ESTA INADIMPLENTE

                            conteudo += "<td style=\"width:400px;\">" + drPrincipal["nomeAluno"].ToString() + "&nbsp;&nbsp;";
                            conteudo += listar(drPrincipal["codigoAluno"].ToString(), drPrincipal["codigoAula"].ToString()) + "</td>\n";


                        }
                        // Fim da exibição dos alunos do grupo

                        if (drPrincipal["aulaRealizada"].ToString() != "False" && drPrincipal["aulaRealizada"].ToString() != "")
                        {
                            conteudo += "<td><input type=checkbox name=" + drPrincipal["codigoAula"].ToString() + "_Efetuar value=" + drPrincipal["codigoAula"].ToString() + " checked></td>\n";
                        }
                        else
                        {
                            conteudo += "<td><input type=checkbox name=" + drPrincipal["codigoAula"].ToString() + "_Efetuar value=" + drPrincipal["codigoAula"].ToString() + "></td>\n";
                        }
                        if (Convert.ToString(drPrincipal["observacoesAula"]) != "")
                        {
                            conteudo += "<td><textarea rows=\"2\" cols=\"10\" name=\"" + drPrincipal["codigoAula"].ToString() + "_aulaObs\">" + drPrincipal["observacoesAula"].ToString() + "</textarea></td>\n";
                        }
                        else
                        {
                            conteudo += "<td><textarea rows=\"2\" cols=\"10\" name=\"" + drPrincipal["codigoAula"].ToString() + "_aulaObs\"></textarea></td>\n";
                        }


                        conteudo += "</tr>\n";
                        conteudo += "</tbody></table></div></td>\n";


                        contador++;

                        Panel1.Controls.Add(new LiteralControl(conteudo));
                        conteudo = "";
                        if (contador == 3)
                        {
                            Panel1.Controls.Add(new LiteralControl("</tr>"));
                            contador = 0;
                            conteudo = "";

                        }


                        horaIniciaAulaAnterior = horaIniciaAula;
                        horaFinalAulaAnterior = horaFinalAula;
                    }

                }
                drPrincipal.Close();
                tabela += " </table>";
                Panel1.Controls.Add(new LiteralControl(tabela));
            }
            catch
            {

            }
            finally
            {
                conexao.Close();
                conexaoGrupo.Close();
                sqlc.Close();
            }

        }

        protected void btnVerificar_Click(object sender, EventArgs e)
        {
            btnSalvar.Visible = true;
            aula();
        }

        void salvar()
        {
            try
            {
                conexao.Open();
                conexaoUpdate.Open();

                for (int i = 1; i < 2; i++)
                {
                    string horaI = "";
                    string horaF = "";
                    string hora = "";

                    switch (i)
                    {
                        case 1:
                            horaI = horaI;
                            horaF = horaF;
                            break;
                        default:
                            break;
                    }


                    sql = "SELECT codigoAula, codigoProfessor, (select nomeProfessor from professor where aula.codigoProfessor = professor.codigoProfessor) as nomeProfessor, \n";
                    sql += "codigoAluno, (select nomeAluno from aluno where aula.codigoAluno = aluno.codigoAluno) as nomeAluno, codigoGrupo, dataAula, horaInicialAula, \n";
                    sql += "horaFinalAula, codigoDisciplina, descricaoAssuntoAula, observacoesAula,  \n";
                    sql += "aulaRealizada, valorHora, valorTotalSemDesconto, valorTotalComDesconto, codigoAnoLetivo, falta, meiaHora \n";
                    sql += "FROM aula WHERE (dataAula = '" + txtData.Text + "') order by CONVERT(INT, SUBSTRING(horaInicialAula,1 , 2))";
                    //or horaFinalAula = '" + txtHoraInicial.Text + 

                    cmdPrincipal = new SqlCommand(sql, conexao);
                    drPrincipal = cmdPrincipal.ExecuteReader();

                    if (drPrincipal.HasRows)
                    {
                        while (drPrincipal.Read())
                        {
                            foreach (string key in Request.Form.AllKeys)
                            {
                                if (key.StartsWith(drPrincipal["codigoAula"].ToString() + "_" + hora + "Efetuar"))
                                {
                                    efetiva = Request.Form[key];
                                }
                                if (key.StartsWith(drPrincipal["codigoAula"].ToString() + "_" + hora + "aulaObs"))
                                {
                                    observacao = Request.Form[key];
                                }
                            }

                            if (efetiva != "" && efetiva != null)
                            {
                                efetiva = "1";
                            }
                            else
                            {
                                efetiva = "0";
                            }
                            update = "update aula set aulaRealizada=" + efetiva + ", observacoesAula='" + observacao + "' where codigoAula = " + drPrincipal["codigoAula"].ToString();

                            cmdUpdate = new SqlCommand(update, conexaoUpdate);
                            cmdUpdate.ExecuteNonQuery();

                            efetiva = "";
                            observacao = "";

                        }
                    }

                    drPrincipal.Close();
                }


            }
            catch
            {

            }
            finally
            {
                conexao.Close();
                conexaoUpdate.Close();
            }

            string literalcc = "<script language=JavaScript>finalizar();</script>";
            Panel1.Controls.Add(new LiteralControl(literalcc));
        }

        protected void btnSalvar_Click(object sender, EventArgs e)
        {
            salvar();
            aula();
        }

        string listar(string codigoAluno, string codigoAula)
        {
            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();
            SqlConnection sqld = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqld.Open();

            string selectAula = "";

            int cont = 0;
            double valorTotal = 0;

            selectAula = "SELECT a.codigoAula, a.codigoProfessor, a.codigoTipoAula, a.meiaHora, ";
            selectAula += "(select nomeProfessor from professor where a.codigoProfessor = professor.codigoProfessor) as nomeProfessor, ";

            selectAula += "(select nomeAluno from aluno where a.codigoAluno = aluno.codigoAluno) as nomeAluno, ";

            selectAula += "a.codigoAluno, a.codigoGrupo, a.dataAula, a.horaInicialAula, a.horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula, ";
            selectAula += "a.aulaRealizada, a.observacoesAula, a.valorHora, a.pago, a.aulaCancelada, a.valorPago, ";
            selectAula += "a.valorTotalSemDesconto, a.valorTotalComDesconto, a.codigoAnoLetivo, a.falta, a.meiaHora, ";

            selectAula += "(select v.aulaValor from valorAula v ";
            selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicial,103) ";
            selectAula += "AND convert(datetime, v.dataFinal,103) and a.codigoTipoAula = v.codigoTipoAula) as valorAula, ";

            selectAula += "(select v.valorValorEspecialAluno from valorEspecialAluno v ";
            selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicialValorEspecialAluno,103) ";
            selectAula += "AND convert(datetime, v.dataFinalValorEspecialAluno,103) and a.codigoAluno = v.codigoAluno) as valorAulaEspecial ";

            selectAula += "FROM aula a ";
            selectAula += "WHERE a.codigoAluno = " + codigoAluno + " and a.aulaRealizada = 1 ";
            selectAula += " order by convert(datetime,a.dataAula,103) desc, CONVERT(INT, SUBSTRING(a.horaInicialAula,1 , 2)) ";

            SqlCommand cmdPrincipal = new SqlCommand(selectAula, sqld);
            SqlDataReader drPrincipal = cmdPrincipal.ExecuteReader();

            if (drPrincipal.HasRows)
            {
                while (drPrincipal.Read())
                {
                    if (drPrincipal["valorAulaEspecial"].ToString().Replace(" ", "") != "")
                    {
                        try
                        {
                            if (Convert.ToBoolean(drPrincipal["meiaHora"]))
                            {
                                valorTotal = valorTotal + (Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString()) / 2);
                            }
                            else
                            {
                                valorTotal += Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString());
                            }
                        }
                        catch
                        {
                        }
                    }
                    else
                    {
                        try
                        {
                            if (Convert.ToBoolean(drPrincipal["meiaHora"]))
                            {
                                valorTotal = valorTotal + (Convert.ToDouble(drPrincipal["valorAula"].ToString()) / 2);
                            }
                            else
                            {
                                valorTotal += Convert.ToDouble(drPrincipal["valorAula"].ToString());
                            }
                        }
                        catch
                        {
                        }
                    }
                }
            }

            drPrincipal.Close();

            cont++;

            sqlc.Close();
            sqld.Close();

            return atualizarValores(codigoAluno, valorTotal);
        }

        string atualizarValores(string codigoAluno, double valorTotal)
        {
            double valorPago = 0;
            double valorDebitoCredito = 0;

            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            string select = "select * from pagamento where codigoAluno = " + codigoAluno;

            SqlCommand cmd = new SqlCommand(select, sqlc);
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    valorPago += Convert.ToDouble(dr["valor"].ToString());
                }
            }

            valorDebitoCredito = valorPago - valorTotal;

            dr.Close();

            sqlc.Close();

            if (valorDebitoCredito < 0)
            {
                return "<span style=\"color:Red;\">(Aluno indadimplente - R$ -" + vV.validar(valorDebitoCredito.ToString().Replace("-", "")) + ")</span><br>";
            }

            return "<br>";
        }
    }
}
